package DataBase;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Vector;

public class GetReportOnWorkHoursOfWaiter {

	private String fromDate;
	private String toDate;
	private Vector<Vector> report;
	private Vector<String>  row;
	private Vector<String>  listDatesOpen;
	private String waiterID;
	
	
	public GetReportOnWorkHoursOfWaiter(String fromDate,String toDate,String ID)
	{
		this.fromDate=fromDate;
		this.toDate=toDate;
		this.waiterID=ID;
		
	}

	
	public Vector<Vector> getReportTablesOfWaiterThis ()
	{
		
        SQLConnection sc=new SQLConnection();
		
		     
        	try {	
        		/*
        		 * list of dateOpen by waiter ID and range date
        		 */
        		PreparedStatement ps = sc.getConnection().prepareStatement("SELECT dateOpen FROM employee_table_timeslot WHERE eid=? AND dateOpen>=? AND dateOpen<? GROUP BY dateOpen ;");
        		ps.setString(1, waiterID);
    			ps.setString(2,fromDate);
    			ps.setString(3,toDate);
        		ResultSet rs=ps.executeQuery();	
        		ResultSetMetaData rsmd = rs.getMetaData();
        		report = new Vector<Vector>();
        		
        		while (rs.next())
        		{
        			listDatesOpen = new Vector<String>();
        			for(int col = 1; col <= rsmd.getColumnCount(); col++) 
		         	{                      	                
        				int type = rsmd.getColumnType(col);
    	                switch(type)
    	                {
    	                
    	                    case Types.INTEGER :
    	                    	listDatesOpen.add(new Integer(rs.getInt(col)).toString());
    	                        break;
    	                        
    	                    case Types.VARCHAR :
    	                    	listDatesOpen.add(rs.getString(col));
    	                        break;
    	                    case Types.DATE :
    	                    	listDatesOpen.add(rs.getDate(col).toString());
    	                        break;    
    	                    case Types.TIME :
    	                    	listDatesOpen.add(rs.getTime(col).toString());
    	                        break;     
    	                }//switch
                    }
        			report.add(listDatesOpen);
        		}
        		
        		 for(int i=0;i<report.size();i++)
        		 {
        		/*
            	 * quantity of opened tables by dateOpen and ID waiter 
            	 */
        			 
        			 PreparedStatement ps1 = sc.getConnection().prepareStatement("SELECT COUNT(ett.tid) FROM employee_table_timeslot ett where ett.dateOpen=? AND ett.eid=?;");	
        		 
        		 /*
        		  * quantity of persons by dateOpen and ID waiter of all opened tables in this date
        		  */
        		 PreparedStatement ps2 = sc.getConnection().prepareStatement("SELECT SUM(no_of_persons)  FROM table_timeslot tt," +  
        		 		                                                     "employee_table_timeslot ett WHERE  tt.dateOpen=ett.dateOpen " +
        		 		                                                     "AND tt.tid=ett.tid AND ett.eid=? AND ett.dateOpen=?;" );
        		 
        		 
        		 /*
        		  * Quantity of this menu
        		  */
        		 
        		PreparedStatement ps3 = sc.getConnection().prepareStatement("SELECT COUNT(oft.mid) " +
        				"FROM order_from_a_table oft,employee_table_timeslot ett where oft.tid=ett.tid AND oft.dateOpen=? AND oft.dateOpen=ett.dateOpen AND oft.mid='1' AND ett.eid=?;");	
        		 
        		          		 
         		 ps1.setString(1, (String) report.get(i).get(0));
         		 ps1.setString(2, waiterID);
         		 
         		 ps2.setString(1, waiterID);
         		 ps2.setString(2, (String) report.get(i).get(0));
         		
         		 ps3.setString(1, (String) report.get(i).get(0));
         		 ps3.setString(2, waiterID);
    			 
     			 ResultSet rs1=ps1.executeQuery();	
     			 ResultSet rs2=ps2.executeQuery();	
     			 ResultSet rs3=ps3.executeQuery();	
     			
     			
      			while( rs1.next() && rs3.next() && rs2.next())
     	        {   
      				
      				 report.get(i).add(rs1.getString(1));
      				 report.get(i).add(rs2.getString(1));
      				if(rs3.getString(1)!=null) 
      					 report.get(i).add(rs3.getString(1));
          			else
          				 report.get(i).add(Integer.toString(0));	
      				
      			   
      			     				
     	        }   //while
        		 }  //for
      			
        		ps.close();
     			rs.close();
     			sc.getConnection().close();
      			
        		 
        		} catch (SQLException e) {
        			// TODO Auto-generated catch block
        			e.printStackTrace();
        		}
        	
        		return report;
        	
        }
        
       

	}
	
	

